package com.tripspins.app.dao;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.tripspins.app.bo.Attachment;
import com.tripspins.app.bo.Contact;
import com.tripspins.app.helper.FILE_TYPE;

import java.util.ArrayList;

/**
 * Created by João.Rutkoski on 18/06/13.
 */
public class AttachmentDAO {

    private SQLiteDatabase database;
    private ContactDAO contactDAO;
    public AttachmentDAO(SQLiteDatabase database) {
        this.database = database;
        contactDAO = new ContactDAO(this.database);
    }

    public void persist(long pinId, ArrayList<Attachment> attachments) {

        for(Attachment att : attachments)
        {
            if(att.getId() <= 0)
            {
                insertAttachments(pinId,attachments);
                //updateSyncedData(pinId);

            }
        }



    }

    private void insertAttachments(long pinId, ArrayList<Attachment> attachments)
    {
        StringBuilder sbInsert = new StringBuilder();
        sbInsert.append("INSERT INTO ATTACHMENTS( ID_PIN ");
        sbInsert.append(                        ",TXT_FILE_PATH " );
        sbInsert.append(                        ",ID_ATTACHMENT_TYPE) " );
        sbInsert.append(                "VALUES ( ? ");//id_pin
        sbInsert.append(                       ", ? ");//TXT_FILE_PATH
        sbInsert.append(                       ", ?);");//ID_ATTACHMENT_TYPE

        for (Attachment att : attachments) {
            if (att.getId() == -1) {

                database.execSQL(sbInsert.toString()
                        , new String[] { String.valueOf(pinId)
                        , att.getPath()
                        , att.getFileType().getValue()});

                att.setId(getLastInsertedRowid());
                if(att.getContact()!=null)
                    contactDAO.persist(att.getId(),att.getContact());
            }
        }
    }

    public void updateSyncedData(long pinId) {

        StringBuilder sb = new StringBuilder();
        sb.append("Update attachments");
        sb.append(" set txt_synced_data = 'Y' ");
        sb.append(" Where id_pin = ?");
        try {
            database.execSQL(sb.toString()
                    , new String[]{String.valueOf(pinId)});
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
    public void updateSyncedFile(long pinId, long attachmentId) {

        StringBuilder sb = new StringBuilder();
        sb.append("Update attachments ");
        sb.append(" set txt_synced_file = 'Y' ");
        sb.append(" Where id_pin = ?");
        sb.append("   and _id = ?");
        database.execSQL(sb.toString()
                , new String[] { String.valueOf(pinId)
                ,String.valueOf(attachmentId)});
    }

    private long getLastInsertedRowid() {

        String query = "select last_insert_rowid() ";
        Cursor cursor = database.rawQuery(query, null);
        cursor.moveToFirst();
        long id = cursor.getLong(0);
        cursor.close();
        return id;
    }

    public ArrayList<Attachment> getAttachmentsByPinId(long id) {
        StringBuilder sb = new StringBuilder();
        sb.append("Select att._id");
        sb.append(    " , att.txt_file_path");
        sb.append(    " , att.ID_ATTACHMENT_TYPE");
        sb.append( " From attachments att");
        sb.append(" Where att.id_pin = ?");
        Cursor cursorAttachments = database.rawQuery(sb.toString(), new String[] { String.valueOf(id) });
        ArrayList<Attachment> attachments = cursorToAttachments(cursorAttachments);
        cursorAttachments.close();

        return attachments;
    }
    private ArrayList<Attachment> cursorToAttachments(Cursor cursor) {
        ArrayList<Attachment> atts = new ArrayList<Attachment>();
        Attachment att;
        Contact contact;
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            long attId = cursor.getLong(0);
            String fileType =cursor.getString(2);
            att = new Attachment();
            att.setId(attId);
            att.setFileType(fileType);
            if(att.getFileType()== FILE_TYPE.CONTACT)
                att.setContact( contactDAO.getContact(attId));
            else
                att.setPath(cursor.getString(1));
            atts.add(att);
            cursor.moveToNext();
        }
        cursor.close();
        return atts;
    }

    public ArrayList<Attachment> getAttachmentsToSyncByPinId(long id) {
        StringBuilder sb = new StringBuilder();
        sb.append("Select att._id");
        sb.append(    " , att.txt_file_path");
        sb.append(    " , att.ID_ATTACHMENT_TYPE");
        sb.append( " From attachments att");
        sb.append(" Where att.id_pin = ?");
        sb.append(" and att.txt_synced_data = 'Y'");
        sb.append(" and att.txt_synced_file = 'N'");
        Cursor cursorAttachments = database.rawQuery(sb.toString(), new String[] { String.valueOf(id) });
        ArrayList<Attachment> attachments = cursorToAttachments(cursorAttachments);
        cursorAttachments.close();

        return attachments;
    }

    public ArrayList<Attachment> getAttachmentsSyncedByPinId(long id) {
        StringBuilder sb = new StringBuilder();
        sb.append("Select att._id");
        sb.append(    " , att.txt_file_path");
        sb.append(    " , att.ID_ATTACHMENT_TYPE");
        sb.append( " From attachments att");
        sb.append(" Where att.id_pin = ?");
        sb.append(" and att.txt_synced_data = 'Y'");
        sb.append(" and att.txt_synced_file = 'Y'");
        Cursor cursorAttachments = database.rawQuery(sb.toString(), new String[] { String.valueOf(id) });
        ArrayList<Attachment> attachments = cursorToAttachments(cursorAttachments);
        cursorAttachments.close();

        return attachments;
    }


    public ArrayList<Attachment> getAttachmentsToSync(long idUser) {
        StringBuilder sb = new StringBuilder();
        sb.append("Select att._id");
        sb.append(    " , att.txt_file_path");
        sb.append(    " , att.ID_ATTACHMENT_TYPE");
        sb.append( " From attachments att");
        sb.append(" Where att.txt_synced_data = 'Y'");
        sb.append("   and att.txt_synced_file = 'N'");
        Cursor cursorAttachments = database.rawQuery(sb.toString(), new String[] { String.valueOf(idUser) });
        ArrayList<Attachment> attachments = cursorToAttachments(cursorAttachments);
        cursorAttachments.close();

        return attachments;
    }
}
